
IF NOT EXISTS( SELECT * FROM sys.schemas WHERE name = 'platform' )
BEGIN
	EXECUTE('CREATE SCHEMA [platform]')
END
	-- GRANT ...
	-- DENY ...
GO

-- Type Sizes
-- FieldNames	= VARCHAR(256)
-- Token		= VARCHAR(16)

--********************************************************
--	Tabela de valores				
--********************************************************
IF OBJECT_ID('[platform].fieldValues', 'U') IS NOT NULL
	DROP TABLE [platform].fieldValues
GO

CREATE TABLE [platform].fieldValues
(
	LoweredFieldName	VARCHAR(256)	NOT NULL, 
	ReversedTokenId		VARCHAR(16)		NOT NULL, 
	Value				NVARCHAR(4000)	NULL, 
	CONSTRAINT PK_fieldValues PRIMARY KEY (LoweredFieldName, ReversedTokenId)
)
GO

--********************************************************
--	Tabela de relacionamento entre as entidades
--********************************************************
IF OBJECT_ID('[platform].dataObjectRelationships', 'U') IS NOT NULL
	DROP TABLE [platform].dataObjectRelationships
GO

CREATE TABLE [platform].dataObjectRelationships
(
	ParentLoweredFieldName	VARCHAR(256)	NOT NULL,	-- Nome do campo (FIELD) que contém o relacionamento
	ParentReversedTokenId	VARCHAR(16)		NOT NULL,	-- ID da entidade (documento) que possui a outra entidade (documento)
	[Order]					INT				NOT NULL,	-- Ordem de apresentação do documento no relacionamento
	ChildLoweredName		VARCHAR(256)	NOT NULL,	-- Nome da entidade (SCHEMA)
	ChildReversedTokenId	VARCHAR(16)		NOT NULL	-- ID da entidade
	CONSTRAINT PK_dataObjectRelationships PRIMARY KEY (ParentLoweredFieldName, ParentReversedTokenId, [Order])
)
GO

-- Indice criado para facilitar as buscas pelas dependências
CREATE UNIQUE INDEX IX_dataObjectRelationships 
	ON [platform].dataObjectRelationships(ChildLoweredName, ChildReversedTokenId)
GO


--********************************************************
--	Tabela de índices por **DateTime**
--********************************************************
IF OBJECT_ID('[platform].fieldDateTimeIndexes', 'U') IS NOT NULL
  DROP TABLE [platform].fieldDateTimeIndexes
GO

CREATE TABLE [platform].fieldDateTimeIndexes
(
	LoweredFieldName	VARCHAR(256)	NOT NULL, 
	ReversedTokenId		VARCHAR(16)		NOT NULL, 
	Value				DATETIME		NULL 
)
GO

CREATE UNIQUE CLUSTERED INDEX IX_fieldDateTimeIndexes 
	ON [platform].fieldDateTimeIndexes(LoweredFieldName, Value DESC, ReversedTokenId)
GO


--********************************************************
--	Tabela de índices por **NVARCHAR(1024)**
--********************************************************
IF OBJECT_ID('[platform].fieldTextIndexes', 'U') IS NOT NULL
  DROP TABLE [platform].fieldTextIndexes
GO

CREATE TABLE [platform].fieldTextIndexes
(
	LoweredFieldName	VARCHAR(256)	NOT NULL, 
	ReversedTokenId		VARCHAR(16)		NOT NULL, 
	Value				NVARCHAR(1024)	NULL
)
GO

CREATE UNIQUE CLUSTERED INDEX IX_fieldTextIndexes
	ON [platform].fieldTextIndexes(LoweredFieldName, Value, ReversedTokenId)
GO


--********************************************************
--	Tabela de índices por **DECIMAL(19,6)**
--********************************************************
IF OBJECT_ID('[platform].fieldNumberIndexes', 'U') IS NOT NULL
  DROP TABLE [platform].fieldNumberIndexes
GO

CREATE TABLE [platform].fieldNumberIndexes
(
	LoweredFieldName	VARCHAR(256)	NOT NULL, 
	ReversedTokenId		VARCHAR(16)		NOT NULL, 
	Value				DECIMAL(19,6)	NULL
)
GO

CREATE UNIQUE CLUSTERED INDEX IX_fieldNumberIndexes
	ON [platform].fieldNumberIndexes(LoweredFieldName, Value, ReversedTokenId)
GO


